package com.services.impl.poi;

import com.config.exception.MyException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;

import java.io.*;

/**
 * Created by Administrator on 2017/9/29.
 */
@Service
public class ExcelService {


    public Workbook getHSSFWorkbook(String filePath) throws MyException {
        File file = new File(filePath);
        Workbook wb;
        try {
            InputStream inputStream = new FileInputStream(file);
            wb = WorkbookFactory.create(inputStream);

        } catch (FileNotFoundException e) {
            throw new MyException("没有找到此文件");
        } catch (IOException e) {
            throw new MyException("创建HSSFWorkbook抛出异常");
        } catch (InvalidFormatException e) {
            throw new MyException(e.toString());
        }
        return wb;
    }


    public String excelToHtmlTable(Sheet sheet) {
        StringBuffer sb = new StringBuffer();
        //统计最多列数，用于对其
        int maxCol = 0;
        for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            if (row.getLastCellNum() > maxCol) {
                maxCol = row.getLastCellNum();
            }
        }

        for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            //空白行
            if (row == null) {
                sb.append("<tr>");
                for (int i = 0; i < maxCol; i++) {
                    sb.append("<td > &nbsp;</td>");
                }
                sb.append("</tr>");
                continue;
            }
            //有数据的行
            sb.append("<tr>");
            for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell == null) {
                    sb.append("<td>&nbsp;</td>");
                    continue;
                }
                String stringValue = getCellValue(cell);
                sb.append("<td>" + stringValue + "</td>");
            }
            for (int i = 0; i < maxCol - row.getLastCellNum(); i++) {
                sb.append("<td > &nbsp;</td>");
            }
            sb.append("</tr>");
        }
        return sb.toString();
    }


    private static String getCellValue(Cell cell) {

        String result = new String();
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:// 数字类型
                double value = cell.getNumericCellValue();
                result = String.valueOf(value);
                break;
            case Cell.CELL_TYPE_STRING:// String类型
                result = cell.getRichStringCellValue().toString();
                break;
            case Cell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                result = "";
                break;
        }
        return result;
    }

}
